How to Get Data from Another Table Using Id from Another Table

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 a ForeignKey field, since Django
will automatically add a "twin" field with an _id suffix. Therefore it should
be company, instead of company_id.

---

Related Topics