Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

How would I write this SELECT statement?

Here's what I've got in my table right now. Take note of the DISTINCT door_id's (389, 287)

shopping cart
I've got another table called doors. Each door id has a corresponding "feature_door_name." I need to write a SELECT statement that starts off with something like this:

select DISTINCT door_id from cart where session_id='i2rnvaoealphebajh9e0jsipb7'

..and do a JOIN of some kind that allows me to retrieve the feature_door_name. That data looks like this:

table
What do you think?
0
brucegust
Asked:
brucegust
3 Solutions
 
SharathData EngineerCommented:
Either INNER or LEFT JOIN, based on your requirement.

SELECT t1.*, c.feature_door_name
   FROM your_table t1
   JOIN cart c ON t1.door_id = c.door_id

Open in new window


SELECT t1.*, c.feature_door_name
   FROM your_table t1
   LEFT JOIN cart c ON t1.door_id = c.door_id

Open in new window

0
 
brucegustPHP DeveloperAuthor Commented:
When I do this:

select distinct c.door_id, d.feature_door_name from cart c INNER JOIN doors d on c.door_id=d.id where c.session_id='i2rnvaoealphebajh9e0jsipb7'

I expect to get to "featurenames" based on the fact that I've got two distinct door_ids in the cart, but I only get one.
0
 
brucegustPHP DeveloperAuthor Commented:
This:

SELECT c.*, d.feature_door_name FROM doors d LEFT JOIN cart c ON d.id = c.door_id and c.session_id='i2rnvaoealphebajh9e0jsipb7'

...gives me this:

too many
I'm missing something. I need to see:

Brandywine Shaker
Ginger Deluxe

..that's it.

What have I got to do?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ryan ChongCommented:
try this
SELECT c.door_id , d.feature_door_name 
FROM cart c LEFT JOIN doors d ON d.id = c.door_id 
Where c.session_id='i2rnvaoealphebajh9e0jsipb7' 
group by c.door_id , d.feature_door_name

Open in new window

0
 
SharathData EngineerCommented:
try other way around for LEFT JOIN.
SELECT c.* , d.feature_door_name 
FROM cart c LEFT JOIN doors d ON d.id = c.door_id 
Where c.session_id='i2rnvaoealphebajh9e0jsipb7' 

Open in new window

0
 
Olaf DoschkeSoftware DeveloperCommented:
Since you start with the doors, you get all doors. Depsite of selecting all door fields as right fields in your field list, it's the "left" table the join starts from, therefore all the NULL rows for doors not having a cart entry. Simply start any query from the main table you want to query and join side data to this, not the other way around.

You might do the RIGHT JOIN instead, but I have to go far back to look where I ever needed this, it's typically only needed in conjunction with at least three tables, as you can of course always switch the order of two tables to make the join a left join. What matters is the order in which tables  are joined in the query, not the orientation of columns in the result.

Bye, Olaf.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now