SQL course

[Bassmansam]

Hi guys

Following an sql course and i am onto joins. I can understand them now but i have a query about this code.

I am selecting from 2 tables but i only have to type "FROM SALES" and not the product table. I know i have called the SALESPERSON name so whats the point of the "FROM" bit if it already knows whats been called?

Hope that makes sense.

SELECT ORDER_ID, S.CUSTOMER_ID, SALESPERSON.FIRST_NAME FROM
SALES S INNER JOIN CUSTOMER C
ON S.CUSTOMER_ID = C.CUSTOMER_ID
LEFT OUTER JOIN SALESPERSON
ON S.SALESPERSON_ID=SALESPERSON.SALESPERSON_ID;

 

[chroniclard]

The from bit is specifying SALES as the main table then an inner join to CUSTOMER and an outer join to SALESPERSON.

Not sure what the question is?

 

[Bassmansam]

The from bit is specifying SALES as the main table then an inner join to CUSTOMER and an outer join to SALESPERSON.

Not sure what the question is?

When you say like that it makes more sense. I just thought when your selecting fields from multiple tables why don't have you have to say from x , y table. Probably just overthinking it.

 

[chroniclard]

Yeah the joins are effectively doing that. Because you have joined Customer you can use C.CustomerFieldName in your SELECT list and the same where you have Salesperson.First_Name.

 

[Bassmansam]

Yeah the joins are effectively doing that. Because you have joined Customer you can use C.CustomerFieldName in your SELECT list and the same where you have Salesperson.First_Name.

Thank you i understand now.

So EA, could easily write a line of code that says at the end of Weekend league who your top scorer is in literally a couple minutes(i recognize you from fifa thread).

 

[chroniclard]

Thank you i understand now.

So EA, could easily write a line of code that says at the end of Weekend league who your top scorer is in literally a couple minutes(i recognize you from fifa thread).

yeah, no idea what their databases are like but using SQL could do something around a GROUP and COUNT to give scorers.

select Player, COUNT(Player) as goals from WLDataTable
WHERE scored = 1 AND TeamId = 'MyTeamName'
group by Player
order by COUNT(Player) desc

Player Goals
Vardy 27
Mbappe 14
Alisson 12
Henderson 2

Add a Top(1)

select Top(1) Player, COUNT(Player) as goals from WLDataTable
WHERE scored = 1 AND TeamId = 'MyTeamName'
group by Player
order by COUNT(Player) desc

Player Goals
Vardy 27

Probably more efficient ways though.

 

[AHarvey]

just a side note to this, formatting can help play a role in understanding it as well

SELECT ORDER_ID, S.CUSTOMER_ID, SALESPERSON.FIRST_NAME FROM
SALES S INNER JOIN CUSTOMER C
ON S.CUSTOMER_ID = C.CUSTOMER_ID
LEFT OUTER JOIN SALESPERSON
ON S.SALESPERSON_ID=SALESPERSON.SALESPERSON_ID;

I would write as

SELECT ORDER_ID, S.CUSTOMER_ID, SALESPERSON.FIRST_NAME
FROM SALES S
INNER JOIN CUSTOMER C
    ON S.CUSTOMER_ID = C.CUSTOMER_ID
LEFT OUTER JOIN SALESPERSON
    ON S.SALESPERSON_ID=SALESPERSON.SALESPERSON_ID;

Others I work with will list the SELECT downwards:

SELECT 
    ORDER_ID,
    S.CUSTOMER_ID,
    SALESPERSON.FIRST_NAME
FROM SALES S

I will do that if I am writing a select for more than about 7 or 8 items purely for readability.

 

[montymint]

regardless of how many tables you want to SELECT from, you always need a FROM.

even if its one table, you still SELECT [Columns, I , Want] FROM [The Table I want]

Then if you need more tables you

SELECT [Columns, I , Want] FROM [The Table I want]
INNER JOIN [ANOTHER TABLE I WANT] ON [TABLE 1.Value] = [TABLE 2.Value]

This assumes that your output MUST have data in both tables to work