How can I get, not null and TRUE results from different tables

Question

1.00/5 (1 vote)

See more:

Im new in SQL but I learn solo. So I need help... What should I do to prevent integers with NULL values from being included in my query?

Im working on swiss_apartments DB it has 2 columns . 1. Locations (Integer) 2. Simulations (boolean) I want to combine two tables and create a new temporary table with datas which I want. But the data is huge and there should only be results as TRUE and NOT NULL values.

Example: I want walkshed_shop_coffee, walkshed_shop_supermarket.... ( a few more) FROM swiss_apartments.locations (which results must be NOT NULL)

and I want SELECT building_id, layout_has_toilet, floor_has_elevator, layout_has_bathtub FROM swiss_apartments.simulations (which results must be TRUE)

after combine (the results which are Not NULL and TRUE) I will be able to found which building_id is the correct one for my result. Supposedly I will find the numbers(building_id) my client needs

Find correct numbers of building_id

What I have tried:

SQL

SELECT count(DISTINCT building_id)
FROM swiss_apartments.simulations 
WHERE layout_has_toilet = 'True' AND floor_has_elevator = 'True' AND layout_has_bathtub = 'True';

**and**

SQL

SELECT *
FROM swiss_apartments.locations
WHERE building_id AND walkshed_shop_coffee AND walkshed_shop_supermarket AND walkshed_amenity_cafe AND walkshed_amenity_kindergarten AND walkshed_amenity_school AND walkshed_amenity_restaurant IS NOT NULL

Posted 11-Apr-24 1:15am

Ebrar Çağdaş

Updated 11-Apr-24 6:45am

Andre Oosthuizen

v2

Add a Solution

Comments

CHill60 11-Apr-24 7:26am

You can make your question clearer by providing some sample data and the results you expect from that data

PIEBALDconsult 11-Apr-24 10:31am

And don't store 'True' as a string -- use a BIT.

Better yet, make them number_of_toilets , number_of_elevators , number_of_bathtubs -- then test against zero.

2 solutions

Solution 3

Based on the comments and Pete's solution -
-Everything depends on whether you have a relation between the 2 tables - Primary and Foreign keys, in your case the 'building_id'. from both tables.
-Store the values as a '0' which will return a false value or a '1' which will return a true value.
-As I will personally use INNER JOIN, read up more on the JOIN operators at W3School | SQL Joins [^]
-To get the count of the distinct' building_ids' you can use 'COUNT(*)' with 'COUNT(DISTINCT building_id)' which you will wrap inside a subquery

Your query should then look like -

SQL

SELECT COUNT(DISTINCT building_id)
FROM (
    SELECT
        s.building_id,
        s.layout_has_toilet,
        s.floor_has_elevator,
        s.layout_has_bathtub,
        l.walkshed_shop_coffee,
        l.walkshed_shop_supermarket,
        l.walkshed_amenity_cafe,
        l.walkshed_amenity_kindergarten,
        l.walkshed_amenity_school,
        l.walkshed_amenity_restaurant
    FROM swiss_apartments.simulations s
    INNER JOIN swiss_apartments.locations l ON s.building_id = l.building_id
    WHERE s.layout_has_toilet = TRUE
      AND s.floor_has_elevator = TRUE
      AND s.layout_has_bathtub = TRUE
      AND l.walkshed_shop_coffee IS NOT NULL
      AND l.walkshed_shop_supermarket IS NOT NULL
      AND l.walkshed_amenity_cafe IS NOT NULL
      AND l.walkshed_amenity_kindergarten IS NOT NULL
      AND l.walkshed_amenity_school IS NOT NULL
      AND l.walkshed_amenity_restaurant IS NOT NULL
) subquery;

Posted 11-Apr-24 7:06am

Andre Oosthuizen

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Accepted Answer · 2024-04-11T02:31:00

Is there any link between these two tables? This is known as a relationship. An example of this might be something like:

I build electric guitars so I have a table that contains a list of all the guitars.
Each guitar has pickups, so I have a separate table containing the individual pickups I could use, and the guitars table has a relationship to these pickups. For simplicity, I'm going to assume that there is a one to one relationship here from the guitar to the pickups.

So, if I want to find the guitar and pickups for my nightingale model, my query would look something like this:

SQL

SELECT ID, Body, Neck, TremSystem, p.PickupName
FROM Guitars g
JOIN Pickups p
ON p.id = g.PickupId
WHERE name = 'Nightingale'

So, I am looking in two tables - Guitar is the master, and Pickups is the related table. I have joined the two tables based on a relationship of the primary key in the pickup table (id), and the relationship in the the Pickup table (Pickups). This is known as a foreign key relationship.

There are different types of join names, INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN. I would suggest that you google these and read up on them.

If you have no relationship, it's going to be difficult to see how you will combine these two tables into one temporary one.

Note the g alias for the guitar isn't strictly necessary, it's just there to differentiate for you that the ON part is using two different tables.