Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How would I write this SELECT statement?

Posted on 2016-08-23
6
Medium Priority
?
89 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 53

Accepted Solution

by:
Ryan Chong earned 1000 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 800 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 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 200 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

722 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