selecting rows with id from another table
Try this (subquery):
SELECT * FROM terms WHERE id IN
(SELECT term_id FROM terms_relation WHERE taxonomy = "categ")
Or you can try this (JOIN):
SELECT t.* FROM terms AS t
INNER JOIN terms_relation AS tr
ON t.id = tr.term_id AND tr.taxonomy = "categ"
If you want to receive all fields from two tables:
SELECT t.id, t.name, t.slug, tr.description, tr.created_at, tr.updated_at
FROM terms AS t
INNER JOIN terms_relation AS tr
ON t.id = tr.term_id AND tr.taxonomy = "categ"
Select rows in a table where id is equals to another id in another table
If the id (primary key) is same for table then you can use join on Id
select * from table1
JOIN table2
on table1.id = table2.id
Use this
select * from swimsuit JOIN getadvocacy ON swimsuit.id= getadvocacy.id;
Result of query is
1 abc 90 1 50,60,70 70
3 def 99 3 60,70,70 70
SQL displaying data from one table based on id from another table
You should do 2 joins, one to get each friend's ID and another to retrieve that friend's name.
SELECT
U.ID,
U.first_name,
U.last_name,
N.first_name FriendFirstName,
N.last_name FriendLastName
FROM
[user] U
LEFT JOIN firends F ON U.ID = F.user_id
LEFT JOIN [user] N ON F.friend_id = N.id
Using a LEFT JOIN
will make you see people with no friends (sniff).
If you want to see a particular user:
SELECT
U.ID,
U.first_name,
U.last_name,
N.first_name FriendFirstName,
N.last_name FriendLastName
FROM
[user] U
LEFT JOIN firends F ON U.ID = F.user_id
LEFT JOIN [user] N ON F.friend_id = N.id
WHERE
U.ID = 1948 -- Supplied ID
SQL Server : get data from another table by joining a table whose ID is active
If I understand your need well, both of this SQL statements should do the work:
SELECT
table1.table1_id,
table1.column_x,
table1.column_y
FROM table1
WHERE table1.column_x IN (SELECT table2_id FROM table2 WHERE IsActive = 1) AND
table1.column_y IN (SELECT table2_id FROM table2 WHERE IsActive = 1)
SELECT
table1.table1_id,
table1.column_x,
table1.column_y
FROM table1
JOIN table2 X ON X.table2_id = table1.column_x
JOIN table2 Y ON Y.table2_id = table1.column_y
WHERE X.IsActive = 1 AND
Y.IsActive = 1
Selecting Items from another table using ID
The syntax of your SQL statement is wrong.
The WHERE
clause must be written after the join:
String selctAllEmployeesOrdersItems =
"SELECT * FROM " + ORDER_ITEM_TABLE + " AS o " +
"INNER JOIN " + ITEM_TABLE + " AS i ON o." + ID_ITEM_ORDER + " = i." + ID_ITEM + " " +
"WHERE o." + ID_ORDER_ITEM + " = " + idOrder;
Note the use of aliases o
and i
for the 2 tables that shortens significantly the code.
Also, the definition of the table ORDER_ITEM
:
String orderItemTable = "CREATE TABLE " + ORDER_ITEM_TABLE + " ("
+ ID_ORDER_ITEM + " INTEGER,"
+ ID_ITEM_ORDER + " INTEGER,"
+ " FOREIGN KEY ("+ID_ORDER_ITEM+") REFERENCES "+ EMP_TABLE +"("+ ID_EMP +"), "
+ " FOREIGN KEY ("+ID_ITEM_ORDER+") REFERENCES "+ EMP_TABLE +"("+ ID_EMP +"));";
does not seem correct.
What is the table EMP_TABLE
?
Why do both columns ID_ORDER_ITEM
and ID_ITEM_ORDER
reference the same column?
This does not make sense.
How to get id of parent tables which haven't any foreign key with another table
The first issue is that you are comparing to an empty string, generally we expect the empty value to be represented by a null
value, so try comparing using IS NULL
select distinct table_id from tables_structure where fk IS NULL
But that isn't likely to help you here, your data represents an UNPIVOT
structure, your second attempt would work if you used a COUNT
in your HAVING
clause, here we don't even have to compare nulls because COUNT
will exclude nulls for us!
select table_id
from tables_structure
group by table_id
having COUNT(fk) = 0
If the values really are empty strings, and not nulls, then we can still use count with nulls by treating ''
as a null value using NULLIF
:
select table_id
from tables_structure
group by table_id
having COUNT(NULLIF(fk,'')) = 0
We can't just filter by fk <> ''
as that will modify the dataset and return ALL records.
You can use a SUM
over a CASE
statement that computes a 1
or 0
for each record, but now things are getting complicated:
select table_id
from tables_structure
group by table_id
having SUM(CASE fk WHEN '' THEN 0 ELSE 1)) = 0
Select rows in a table where id is equals to another id in another table and sum values in column from the result
I would suggest JOIN
and GROUP BY
:
SELECT SUM(fd.qty)
FROM facturedet fd JOIN
facture f
ON fd.facture_id = f.rowid
WHERE date_valid >= current_date AND
date_valid < current_date INTERVAL '1 DAY';
Note: This uses inequalities for the date comparison. The exact method for adding one date varies by database. In most databases, this structure for the comparison is better for the optimizer, which means a faster query.
How to get table data based on id which obtains from another table data? Django
You can filter with:
Vehicles.objects.filter(companycontainvehicles__company_id=company_id)
Here your companycontainvehicles
basically acts as a ManyToManyField
. You can span a many-to-many relation between Vehicle
and Company
with:
class Company(models.Model):
name = models.CharField(max_length=255)
slug = models.SlugField(blank=True, null=True, unique=True)
description = models.TextField()
class Vehicle(models.Model):
vehicle_number = models.IntegerField()
name = models.CharField(max_length=255)
slug = models.SlugField(blank=True, null=True, unique=True)
companies = models.ManyToManyField(
Company,
through='CompanyVehicle',
related_name='companies'
)
class CompanyVehicle(models.Model):
company = models.ForeignKey(Company, on_delete=models.CASCADE)
vehicle = models.ForeignKey(Vehicle, on_delete=models.CASCADE)
created_at = models.DateTimeField(auto_now_add=True)
Then you can filter with:
Vehicle.objects.filter(companies=company_id)
Note: normally a Django model is given a singular name, so
Vehicle
instead of.Vehicles
Note: Normally one does not add a suffix
_id
to aForeignKey
field, since Django
will automatically add a "twin" field with an_id
suffix. Therefore it should
becompany
, instead of.company_id
Related Topics
Fatal Error: Call to Undefined Function Sqlsrv_Connect()
Check If a Specific Value Exists At a Specific Key in Any Subarray of a Multidimensional Array
Laravel Storage Link Won't Work on Production
Preventing Direct Access to a PHP Page, Only Access If Redirected
Telegram Botapi, Send Message to Multiple Chat_Id
Laravel: Display Difference Between Two Dates in Blade
Get Woocommerce Carts Total Amount
How to Prevent Multiple Inserts When Submitting a Form in PHP
Php Get Number of Week for Month
How to Do Multilevel Menu the Correct Way With MySQL PHP
Php How to Loop Through a Post Array
How to Run Angular App on Xampp Server
How to Get Values of Multiple Selected (Dynamic) Checkbox in PHP
Keep Values Selected After Form Submission
How Would I Skip Optional Arguments in a Function Call
How to Insert Special Character in MySQL Via PHP and Display on HTML Page