Solved

How would I write this SELECT statement?

Posted on 2016-08-23
6
83 Views
Last Modified: 2016-08-24
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
Comment
Question by:brucegust
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 41767984
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
 

Author Comment

by:brucegust
ID: 41767986
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
 

Author Comment

by:brucegust
ID: 41767991
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 52

Accepted Solution

by:
Ryan Chong earned 250 total points
ID: 41768179
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
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 200 total points
ID: 41768416
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
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 50 total points
ID: 41768485
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

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question